Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

' Para gerar Excel
Imports System
Imports System.IO
Imports System.Text
Imports Microsoft.Office.Interop

Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls

' Para gerar Excel
Imports System.Reflection
Imports System.Runtime.InteropServices

Partial Class ImpRelJournalEntry
    Inherits System.Web.UI.Page

    Private Function ExcluiJournalEntryHeader(ByVal strConn As String) As Boolean

        Dim strSQL As String

        ExcluiJournalEntryHeader = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                strSQL = "DELETE FROM JournalEntryHeader" & _
                         " WHERE CodigoDaCia = '" & Session("strCodigoDaCia").ToString & "'" & _
                         " AND MesAnoDeCompetencia = '" & Session("strMesAnoDeCompetencia").ToString & "'" & _
                         " AND CreateUser = '" & Session("sNM_USERNICKNAME").ToString & "';"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theSqlCommand.CommandText = strSQL

                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                theSqlDataReader.Close()

                ExcluiJournalEntryHeader = True

            Catch ex As Exception
                Session.Add("MsgException", ex.Message)
            End Try

        End Using

    End Function

    Private Function ExcluiJournalEntryDetail(ByVal strConn As String) As Boolean

        Dim strSQL As String

        ExcluiJournalEntryDetail = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                strSQL = "DELETE FROM JournalEntryDetail" & _
                         " WHERE CodigoDaCia = '" & Session("strCodigoDaCia").ToString & "'" & _
                         " AND MesAnoDeCompetencia = '" & Session("strMesAnoDeCompetencia").ToString & "'" & _
                         " AND CreateUser = '" & Session("sNM_USERNICKNAME").ToString & "';"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theSqlCommand.CommandText = strSQL

                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                theSqlDataReader.Close()

                ExcluiJournalEntryDetail = True

            Catch ex As Exception
                Session.Add("MsgException", ex.Message)
            End Try

        End Using

    End Function

    Private Function IncluiJournalEntryHeader(ByVal strConn As String) As Boolean

        Dim strSQL As String = ""

        IncluiJournalEntryHeader = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                strSQL = "INSERT INTO JournalEntryHeader " & _
                         " (" & _
                         " CodigoDaCia," & _
                         " MesAnoDeCompetencia," & _
                         " RecordType," & _
                         " CompanyCode," & _
                         " DocumentDate," & _
                         " Currency," & _
                         " PostingDate," & _
                         " Reference," & _
                         " DollarAmount," & _
                         " DocumentType," & _
                         " CreateUser," & _
                         " Created" & _
                         " )"

                strSQL = strSQL & _
                         " SELECT" & _
                         " '" & Session("strCodigoDaCia").ToString & "'," & _
                         " '" & Session("strMesAnoDeCompetencia").ToString & "'," & _
                         " 'H'," & _
                         " MAX(CodigoDaCiaNaAtividade) AS CodigoDaCiaNaAtividade," & _
" SUBSTRING(CONVERT(VARCHAR(30),DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, '" & Right(Session("strMesAnoDeCompetencia").ToString, 4) & "/" & Left(Session("strMesAnoDeCompetencia").ToString, 2) & "/" & "01') + 1, 0)),102), 1 ,4)" & _
" + SUBSTRING(CONVERT(VARCHAR(30),DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, '" & Right(Session("strMesAnoDeCompetencia").ToString, 4) & "/" & Left(Session("strMesAnoDeCompetencia").ToString, 2) & "/" & "01') + 1, 0)),102), 6 ,2)" & _
" + SUBSTRING(CONVERT(VARCHAR(30),DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, '" & Right(Session("strMesAnoDeCompetencia").ToString, 4) & "/" & Left(Session("strMesAnoDeCompetencia").ToString, 2) & "/" & "01') + 1, 0)),102), 9 ,2)" & _
                         " ,'BRL'," & _
" SUBSTRING(CONVERT(VARCHAR(30),DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, '" & Right(Session("strMesAnoDeCompetencia").ToString, 4) & "/" & Left(Session("strMesAnoDeCompetencia").ToString, 2) & "/" & "01') + 1, 0)),102), 1 ,4)" & _
" + SUBSTRING(CONVERT(VARCHAR(30),DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, '" & Right(Session("strMesAnoDeCompetencia").ToString, 4) & "/" & Left(Session("strMesAnoDeCompetencia").ToString, 2) & "/" & "01') + 1, 0)),102), 6 ,2)" & _
" + SUBSTRING(CONVERT(VARCHAR(30),DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, '" & Right(Session("strMesAnoDeCompetencia").ToString, 4) & "/" & Left(Session("strMesAnoDeCompetencia").ToString, 2) & "/" & "01') + 1, 0)),102), 9 ,2)" & _
                         " ,'Rateio de despesas'," & _
                         " " & 0 & "," & _
                         " 'ZZ'," & _
                         " '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                         " GETDATE()" & _
                         " FROM RateioDeDespesas" & _
                         " WHERE MesAnoDeCompetencia = '" & Session("strMesAnoDeCompetencia").ToString & "'" & _
                         " AND CodigoDaCia = '" & Session("strCodigoDaCia").ToString & "';"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theSqlCommand.CommandText = strSQL

                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                theSqlDataReader.Close()

                IncluiJournalEntryHeader = True

            Catch ex As Exception
                Session.Add("MsgException", ex.Message)
            End Try

        End Using

    End Function

    Private Function IncluiJournalEntryDetail(ByVal strConn As String) As Boolean

        Dim strSQL As String = ""

        IncluiJournalEntryDetail = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                strSQL = "INSERT INTO JournalEntryDetail" & _
                         " (" & _
                         " CodigoDaCia," & _
                         " MesAnoDeCompetencia," & _
                         " RecordType," & _
                         " AccountNumber," & _
                         " CompanyCode," & _
                         " ProfitCenter," & _
                         " CostCenter," & _
                         " DollarAmount," & _
                         " CreateUser," & _
                         " Created" & _
                         " )"

                strSQL = strSQL & _
                         " SELECT" & _
                         " '" & Session("strCodigoDaCia").ToString & "'," & _
                         " '" & Session("strMesAnoDeCompetencia").ToString & "'," & _
                         " 'D' AS RecordType," & _
                         " CodigoDaConta AS AccountNumber," & _
                         " CodigoDaCiaNaAtividade AS CompanyCode," & _
                         " '100500' AS ProfitCenter," & _
                         " CodigoDoCentroDeCusto AS CostCenter," & _
                         " SUM(ValorCalculadoDoRateio) AS DollarAmount," & _
                         " '" & Session("sNM_USERNICKNAME").ToString & "' AS CreateUser," & _
                         " GETDATE() AS Created" & _
                         " FROM RateioDeDespesas" & _
                         " WHERE MesAnoDeCompetencia = '" & Session("strMesAnoDeCompetencia").ToString & "'" & _
                         " AND CodigoDaCia = '" & Session("strCodigoDaCia").ToString & "'"

                ' CAMG Info | Marcelo Barcelos | 2012.05.04 
                'Codigo colocado devido a mudanca do calculo do rateio CalculoDeRateioDeDespesas.aspx.vb
                strSQL = strSQL & _
                         " AND CodigoDaCiaNaAtividade <> CodigoDaCia" & _
                         " GROUP BY CodigoDaCiaNaAtividade, CodigoDoCentroDeCusto, CodigoDaConta, MesAnoDeCompetencia;"

                ' Original
                '" GROUP BY CodigoDoCentroDeCusto, CodigoDaConta, MesAnoDeCompetencia;"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theSqlCommand.CommandText = strSQL

                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                theSqlDataReader.Close()

                IncluiJournalEntryDetail = True

            Catch ex As Exception
                Session.Add("MsgException", ex.Message)
            End Try

        End Using

    End Function

    Private Function CarregaGVWHeader(ByVal strMesAnoDeCompetencia As String, ByVal strCodigoDaCia As String) As Boolean

        Dim strSQL As String

        CarregaGVWHeader = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                strSQL = "SELECT RecordType, CompanyCode, DocumentDate, Currency, PostingDate," & _
                         " Reference, ShortText, DollarAmount, DocumentType, SpecialPeriod" & _
                         " FROM JournalEntryHeader" & _
                         " WHERE CodigoDaCia = '" & Session("strCodigoDaCia").ToString & "'" & _
                         " AND MesAnoDeCompetencia = '" & Session("strMesAnoDeCompetencia").ToString & "'" & _
                         " AND CreateUser = '" & Session("sNM_USERNICKNAME").ToString & "';"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                theSqlCommand.CommandText = strSQL

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Dim mydatatable As New DataTable

                mydatatable.Load(theSqlDataReader)

                gvwHeader.DataSource = mydatatable
                gvwHeader.DataBind()

                CarregaGVWHeader = True

            Catch ex As Exception
                Session.Add("MsgException", ex.Message)
                'If Request.IsLocal Then
                '    Session("CurrentError") = ex.Message
                'Else
                '    Session("CurrentError") = "Error processing page."
                'End If
                'Server.Transfer("Login.aspx")
            Finally
                theSqlConnection.Close()
            End Try

        End Using

    End Function

    Private Function CarregaGVWDetail(ByVal strMesAnoDeCompetencia As String, ByVal strCodigoDaCia As String) As Boolean

        Dim strSQL As String

        CarregaGVWDetail = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                strSQL = "SELECT RecordType, AccountNumber, TaxCode, CompanyCode," & _
                         " BusinessArea, ProfitCenter, CostCenter, InternalOrder," & _
                         " WBSElement, DollarAmount, AssigmentNumber, TextDescription," & _
                         " Customer, Material, TTWCountry" & _
                         " FROM JournalEntryDetail" & _
                         " WHERE CodigoDaCia = '" & Session("strCodigoDaCia").ToString & "'" & _
                         " AND MesAnoDeCompetencia = '" & Session("strMesAnoDeCompetencia").ToString & "'" & _
                         " AND CreateUser = '" & Session("sNM_USERNICKNAME").ToString & "'" & _
                         " ORDER BY CostCenter, AccountNumber;"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                theSqlCommand.CommandText = strSQL

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Dim mydatatable As New DataTable

                mydatatable.Load(theSqlDataReader)

                gvwDetail.DataSource = mydatatable
                gvwDetail.DataBind()

                CarregaGVWDetail = True

            Catch ex As Exception
                Session.Add("MsgException", ex.Message)
                'If Request.IsLocal Then
                '    Session("CurrentError") = ex.Message
                'Else
                '    Session("CurrentError") = "Error processing page."
                'End If
                'Server.Transfer("Login.aspx")
            Finally
                theSqlConnection.Close()
            End Try

        End Using

    End Function

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        If Not Page.IsPostBack Then

            Dim strConnection As String = ""

            strconnection = "Database=liion;Data Source=localhost;User Id=" & Session("strUserID") & ";Password=" & Session("strPassWord")

            'btnImprimir.Enabled = False

            If Not (ExcluiJournalEntryHeader(strConnection) = True) Then
                Exit Sub
            End If

            If Not (ExcluiJournalEntryDetail(strConnection) = True) Then
                Exit Sub
            End If

            If Not (IncluiJournalEntryHeader(strConnection) = True) Then
                Exit Sub
            End If

            If Not (IncluiJournalEntryDetail(strConnection) = True) Then
                Exit Sub
            End If

            If (CarregaGVWHeader(Session("strMesAnoDeCompetencia").ToString, Session("strCodigoDaCia").ToString) = True) Then

                If Not (gvwHeader.PageCount = 0) Then

                    If (CarregaGVWDetail(Session("strMesAnoDeCompetencia").ToString, Session("strCodigoDaCia").ToString) = True) Then

                        If (gvwDetail.PageCount = 0) Then
                            Response.Redirect("RelJournalEntry.aspx")
                        Else
                            ExportaExcel01()
                            'Response.Redirect("RelPesoDoEmpregadoNoCentroDeCusto.aspx")
                        End If

                    End If

                End If

            End If

        End If

    End Sub

    Private Function ExportaExcel01() As Boolean

        Try
            Dim frm As HtmlForm = New HtmlForm

            'gvwData.AllowPaging = False
            'gvwData.DataBind()

            Response.Clear()
            Response.AddHeader("content-disposition", "attachment; filename=FileName.xls")
            Response.Charset = ""
            'Response.ContentType = "application/vnd.xls"
            Response.ContentType = "application/vnd.ms-excel"

            ' If you want the option to open the Excel file without saving than comment out the line below
            Response.Cache.SetCacheability(HttpCacheability.NoCache)

            'Response.ContentType = "application/vnd.xls"

            'gvwData.Page.EnableViewState = False
            EnableViewState = False
            'Controls.Add(frm)
            'frm.Controls.Add(gvwData)

            'Dim strBuilder As New StringBuilder("")
            Dim stringWrite As New StringWriter()
            'System.IO.StringWriter(stringWrite = New System.IO.StringWriter())

            Dim htmlWrite As New HtmlTextWriter(stringWrite)
            'System.Web.UI.HtmlTextWriter(htmlWrite = New HtmlTextWriter(stringWrite))

            'frm.RenderControl(htmlWrite)
            gvwDetail.RenderControl(htmlWrite)

            Response.Write(stringWrite.ToString())

            Response.End()

            'gvwData.AllowPaging = True
            'gvwData.DataBind()

        Catch ex As Exception
            'lblMsg.Text = ex.Message
        End Try

    End Function

End Class
